drop table users;
drop table roles;
drop table user_roles;
drop table CustomerDetails;
drop table RepDetails;
drop table ProductDetails;
create table users
(
  user_name varchar(15) not null primary key,
  user_pass varchar(15) not null
);


create table roles
(
  role_name varchar(15) not null primary key
);

create table user_roles
(
  user_name varchar(15) not null,
  role_name varchar(15) not null,
  primary key( user_name, role_name )
);
create table ProductDetails(
pid varchar(4) not null primary key,
product_type varchar(15) not null,
description varchar(30) not null,
COST VARCHAR(15) NOT NULL,
contract numeric not null
);

create table  CustomerDetails
(
USER_ID VARCHAR(15) NOT NULL PRIMARY KEY,
user_name varchar(15) CONSTRAINT FK_UNC REFERENCES users(user_name),
first_name varchar(15) not null,
last_name varchar(15) not null,
ssn varchar(15) not null,
service_address varchar(15) not null,
billing_address varchar(15) not null,
primary_phone varchar(15) not null,
alternate_phone varchar(15),
email varchar(15) not null,
security_question varchar(100) not null,
SECURITY_ANSWER VARCHAR(100) NOT NULL,
PASSCODE VARCHAR(15) NOT NULL,
PRODUCT_TV VARCHAR(4) CONSTRAINT FK_TV REFERENCES PRODUCTDETAILS(PID),
product_internet varchar(4) CONSTRAINT fk_int REFERENCES ProductDetails(pid),
start_date date not null
);
CREATE TABLE REPDETAILS(
user_id varchar(4) not null primary key,
user_name varchar(15) CONSTRAINT FK_UNR REFERENCES users(user_name),
first_name varchar(15) not null,
last_name varchar(15) not null,
email varchar(15)
);
CREATE SEQUENCE seq_customer_id START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER BI_CUSTOMER_ID
BEFORE INSERT ON customerdetails
FOR EACH ROW
BEGIN
    SELECT replace('C'||to_char(SEQ_customer_ID.NEXTVAL,'099'),' ',''),sysdate INTO :NEW.user_id,:NEW.start_date FROM DUAL;
END;
insert into users values ('john', 'pass');
insert into roles values ('user');
insert into user_roles values ('john', 'user');
insert into RepDetails(user_id,first_name, last_name) values('R001','Roger','Zhang');
insert into ProductDetails values ('P001','TV', 'Basic Plan - 20 Channels', '14.99', 6);
insert into ProductDetails values ('P002','TV', 'Economic Plan - 50 Channels', '24.99', 6);
insert into ProductDetails values ('P003','TV', 'Premium Plan - 100 Channels', '39.99', 6);
insert into ProductDetails values ('P004','INT', '20 Mbps', '14.99', 6);
insert into ProductDetails values ('P005','INT', '30 Mbps', '24.99', 6);
insert into ProductDetails values ('P006','INT', '45 Mbps', '34.99', 6);
INSERT INTO PRODUCTDETAILS VALUES ('P007','INT', '70 Mbps', '49.99', 6);
COMMIT;
